package edu.hubu.dao.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.sql.PreparedStatement;

import edu.hubu.dao.UserDao;
import edu.hubu.exception.IdIsNullException;
import edu.hubu.model.Users;
import edu.hubu.util.C3P0Util;
import edu.hubu.util.DbHelper;
import edu.hubu.util.JdbcUtil;

public class UserDaoImpl2 implements UserDao {

    @Override
    public int addUser(Users users) {

        int res = 0;

        try {
            String sql = "insert into users(name,nickName,pwd,gender,birthday,hobby,tel,email,grade,description) "
                    + "values(?,?,?,?,?,?,?,?,?,?)";
            Object[] params = {users.getName(), users.getNickName(),
                    users.getPwd(), users.getGender(), users.getBirthday(),
                    users.getHobby(), users.getTel(), users.getEmail(),
                    users.getGrade(), users.getDescription()};
            res = DbHelper.executeUpdate(sql, params);
            // 5、遍历结果集

            // 6、关闭资源
            // JdbcUtil.release(DbHelper.getPs(), DbHelper.getConn(), rs);
            // C3P0Util.release(DbHelper.getPs(), DbHelper.getConn(), rs);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return res;

    }

    @Override
    public int delUserById(Integer id) throws IdIsNullException {

        if (id == null) {
            throw new IdIsNullException("根据id删除单个用户时，id不能为空！");
        }

        int res = 0;

        try {
            String sql = "delete from users where id=?";
            Integer[] params = {id};
            res = DbHelper.executeUpdate(sql, params);
            // 5、遍历结果集

            // 6、关闭资源
            // JdbcUtil.release(DbHelper.getPs(), DbHelper.getConn(), rs);
            // C3P0Util.release(DbHelper.getPs(), DbHelper.getConn(), rs);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return res;
    }

    @Override
    public int updateById(Users users) throws IdIsNullException {
        // TODO Auto-generated method stub
        return 0;
    }

    @Override
    public Users getUserByName(String name, String pwd) {

        Users user = null;

        try {
            String sql = "select * from users where name=? and pwd=?";
            String[] params = {name, pwd};
            ResultSet rs = DbHelper.executeQuery(sql, params);
            //5、遍历结果集
            if (rs.next()) {
                // 封装
                user = new Users();

                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setNickName(rs.getString("nickName"));
                user.setPwd(rs.getString("pwd"));
                user.setGender(rs.getString("gender"));
                user.setBirthday(rs.getDate("birthday"));
                user.setHobby(rs.getString("hobby"));
                user.setTel(rs.getString("tel"));
                user.setEmail(rs.getString("email"));
                user.setGrade(rs.getInt("grade"));
                user.setDescription(rs.getString("description"));
            }

            //6、关闭资源
//			JdbcUtil.release(DbHelper.getPs(), DbHelper.getConn(), rs);
            C3P0Util.release(DbHelper.getPs(), DbHelper.getConn(), rs);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return user;
    }

    @Override
    public List<Users> getAllUsers() {

        List<Users> users = null;

        Users user = null;

        try {
            String sql = "select * from users";
            ResultSet rs = DbHelper.executeQuery(sql, null);
            //5、遍历结果集
            users = new ArrayList<Users>();
            while (rs.next()) {
                // 封装
                user = new Users();

                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setNickName(rs.getString("nickName"));
                user.setPwd(rs.getString("pwd"));
                user.setGender(rs.getString("gender"));
                user.setBirthday(rs.getDate("birthday"));
                user.setHobby(rs.getString("hobby"));
                user.setTel(rs.getString("tel"));
                user.setEmail(rs.getString("email"));
                user.setGrade(rs.getInt("grade"));
                user.setDescription(rs.getString("description"));

                users.add(user);
            }

            //6、关闭资源
//			JdbcUtil.release(DbHelper.getPs(), DbHelper.getConn(), rs);
            C3P0Util.release(DbHelper.getPs(), DbHelper.getConn(), rs);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return users;
    }

    @Override
    public List<Users> getPageUsers(int pageNum, int pageSize) {

        List<Users> users = null;

        Users user = null;

        try {
            String sql = "select * from users limit ?,?";
            Integer[] params = {(pageNum - 1) * pageSize, pageSize};
            ResultSet rs = DbHelper.executeQuery(sql, params);
            // 5、遍历结果集
            users = new ArrayList<Users>();
            while (rs.next()) {
                // 封装
                user = new Users();

                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setNickName(rs.getString("nickName"));
                user.setPwd(rs.getString("pwd"));
                user.setGender(rs.getString("gender"));
                user.setBirthday(rs.getDate("birthday"));
                user.setHobby(rs.getString("hobby"));
                user.setTel(rs.getString("tel"));
                user.setEmail(rs.getString("email"));
                user.setGrade(rs.getInt("grade"));
                user.setDescription(rs.getString("description"));

                users.add(user);
            }

            // 6、关闭资源
            // JdbcUtil.release(DbHelper.getPs(), DbHelper.getConn(), rs);
            C3P0Util.release(DbHelper.getPs(), DbHelper.getConn(), rs);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return users;

    }

    @Override
    public int getPageCount(int pageSize) {

        int res = 0;

        try {
            String sql = "select count(*) from users";
            ResultSet rs = DbHelper.executeQuery(sql, null);
            // 5、遍历结果集
            if (rs.next()) {
                int count = rs.getInt(1);
                if (count % pageSize == 0) {
                    res = count / pageSize;
                } else {
                    res = count / pageSize + 1;
                }

            }

            // 6、关闭资源
            // JdbcUtil.release(DbHelper.getPs(), DbHelper.getConn(), rs);
            C3P0Util.release(DbHelper.getPs(), DbHelper.getConn(), rs);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return res;

    }

}
